﻿using MgSoft.Import.Exchange.ExWebApi.Db.Dtos;
using System;
using System.Collections.Generic;
using System.Text;
using Dapper;
using Autofac;
using MgSoft.Configure;
using System.Data;
using MgSoft.Configure.Dtos;
using System.Linq;
using System.Data.SqlClient;
using Newtonsoft.Json;

namespace MgSoft.Import.Exchange.ExWebApi.Db.Daos
{
    public class SyncDataDao : ISyncDataDao
    {
        private string connString;

        public SyncDataDao(ILifetimeScope lifetimeScope, ConfigDto configDto)
        {
            connString = configDto.ConnString_Default;
        }


        /// <summary>
        /// 从数据库取数
        /// </summary>
        /// <typeparam name="TDto"></typeparam>
        /// <returns></returns>
        public List<TDto> GetSyncDatas<TDto>(object obj) where TDto : class
        {
            var sqlString = "SELECT * FROM SyncData WHERE State>0  AND Biz_BillType=@BillType ORDER BY CreateTime DESC";
            List<SyncDataDto> syncDataDtoList;
            using (var dbConnection = new SqlConnection(connString))
            {
                syncDataDtoList = dbConnection.Query<SyncDataDto>(sqlString, new { BillType = obj.ToString() }).ToList();
            }
            return syncDataDtoList.Select(p => JsonConvert.DeserializeObject<TDto>(p.Biz_JsonData)).ToList();
        }

        /// <summary>
        /// 写入数据库
        /// </summary>
        /// <typeparam name="TDto"></typeparam>
        /// <param name="dto"></param>
        public void AddSyncData(SyncDataDto dto)
        {
            #region sql
            var addSqlString =
@"INSERT INTO [dbo].[SyncData]
           ([CreateTime]
           ,[FirstSyncSucceTime]
           ,[LastSyncSucceTime]
           ,[State]
           ,[LastUpdateTime]
           ,[IsSyncError]
           ,[ErrorMessage]
           ,[ErrorStackTrace]
           ,[SyncCount]
           ,[SyncTime]
           ,[CostTime]
           ,[OrgNumber]
           ,[Biz_KeyField]
           ,[Biz_JsonData]
           ,[Biz_Date]
           ,[Biz_UpdateField]
           ,[Biz_BillType]
           ,[Biz_BillTypeName])
     VALUES
           (@CreateTime
           ,@FirstSyncSucceTime
           ,@LastSyncSucceTime
           ,@State
           ,@LastUpdateTime
           ,@IsSyncError
           ,@ErrorMessage
           ,@ErrorStackTrace
           ,@SyncCount
           ,@SyncTime
           ,@CostTime
           ,@OrgNumber
           ,@Biz_KeyField
           ,@Biz_JsonData
           ,@Biz_Date
           ,@Biz_UpdateField
           ,@Biz_BillType
           ,@Biz_BillTypeName)";
            #endregion           
            using (var dbConnection = new SqlConnection(connString))
            {
                dbConnection.Execute(addSqlString, dto);
            }
        }

        public bool IsExist(string keyFieldValue, string orgNumberValue, string Biz_BillType)
        {
            string sqlString = "SELECT Id FROM SyncData WHERE Biz_KeyField = @Biz_KeyField AND OrgNumber = @OrgNumber AND Biz_BillType=@Biz_BillType";
            using (var dbConnection = new SqlConnection(connString))
            {
                return dbConnection.Query<int>(sqlString, new { Biz_KeyField = keyFieldValue, OrgNumber = orgNumberValue, Biz_BillType = Biz_BillType }).Any();
            }
        }
        
        public SyncDataDto Get(string keyFieldValue, string orgNumberValue, string Biz_BillType)
        {
            string sqlString = "SELECT * FROM SyncData WHERE Biz_KeyField = @Biz_KeyField AND OrgNumber = @OrgNumber AND Biz_BillType=@Biz_BillType";
            using (var dbConnection = new SqlConnection(connString))
            {
                return dbConnection.Query<SyncDataDto>(sqlString, new { Biz_KeyField = keyFieldValue, OrgNumber = orgNumberValue,Biz_BillType = Biz_BillType }).SingleOrDefault();
            }
        }

        public void Update(SyncDataDto syncDataDto)
        {
            #region 

            /*不需要更新
              ,[Biz_BillType] = @Biz_BillType
            ,[Biz_BillTypeName] = @Biz_BillTypeName*/
            string sqlString = @"UPDATE [dbo].[SyncData]
SET [CreateTime] = @CreateTime
      ,[FirstSyncSucceTime] = @FirstSyncSucceTime
      ,[LastSyncSucceTime] = @LastSyncSucceTime
      ,[State] = @State
      ,[LastUpdateTime] = @LastUpdateTime
      ,[IsSyncError] = @IsSyncError
      ,[ErrorMessage] = @ErrorMessage
      ,[ErrorStackTrace] = @ErrorStackTrace
      ,[SyncCount] = @SyncCount
      ,[SyncTime] = @SyncTime
      ,[CostTime] = @CostTime
      ,[OrgNumber] = @OrgNumber
      ,[Biz_KeyField] = @Biz_KeyField
      ,[Biz_JsonData] = @Biz_JsonData
      ,[Biz_Date] = @Biz_Date
      ,[Biz_UpdateField] = @Biz_UpdateField
 WHERE Id=@Id";
            #endregion
            using (var dbConnection = new SqlConnection(connString))
            {
                dbConnection.Execute(sqlString, syncDataDto);
            }
        }
    }
}
